﻿using Oracle.ManagedDataAccess.Client;
using System;
using System.Data;

namespace SourceMind
{
    class OracleHelper
    {

        private OracleConnection conn;

        //数据库连接模块
        public string Connect(string hostaddress, string postaddress, string servername, string uid, string pwd)
        {
            string connString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + hostaddress + ")(PORT=" + postaddress + "))(CONNECT_DATA=(SID=" + servername + ")));Persist Security Info=True;User ID=" + uid + ";Password=" + pwd + ";";
            try
            {

                conn = new OracleConnection(connString);
                conn.Open();

                return "连接数据库成功！";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        //数据库释放资源模块
        public string Oracle_Close()
        {
            try
            {
                if (conn == null)
                {
                    return "无数据库连接！";
                }
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
                else
                {
                    if (conn.State == ConnectionState.Closed)
                    {
                        return "数据库已被断开";
                    }
                    if (conn.State == ConnectionState.Broken)
                    {
                        return "数据库连接被破坏！";
                    }
                }
                return "已断开数据库！";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        //数据库插入模块
        public string Oracle_Insdelupd(string sql1, string sql2, string sql3)
        {
            try
            {
                int num = 0;
                if (conn == null)
                {
                    return "请先连接数据库！！";
                }
                if (conn.State == ConnectionState.Open)
                {
                    OracleCommand oracleCommand1 = new OracleCommand(sql1, conn);
                    num = oracleCommand1.ExecuteNonQuery();
                    if (sql2 != "")
                    {
                        OracleCommand oracleCommand2 = new OracleCommand(sql2, conn);
                        num = num + oracleCommand2.ExecuteNonQuery();
                    }
                    if (sql3 != "")
                    {
                        OracleCommand oracleCommand3 = new OracleCommand(sql3, conn);
                        num = num + oracleCommand3.ExecuteNonQuery();
                    }
                }
                else
                {
                    if (conn.State == ConnectionState.Closed)
                    {
                        return "Database connection closed";
                    }
                    if (conn.State == ConnectionState.Broken)
                    {
                        return "Database connection is destroyed";
                    }
                }
                return "成功绑定" + num + "组数据！";
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
                string o = "绑定数据失败，请检查数据格式是否正确！！";
                return o;
            }
        }

        //数据库查询模块
        public DataSet Oracle_Select(string sql, out string record)
        {
            try
            {
                DataSet dataSet = new DataSet();
                if (conn != null)
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(sql, conn);
                        oracleDataAdapter.Fill(dataSet, "sample");
                        oracleDataAdapter.Dispose();
                        record = "OK";
                        return dataSet;
                    }
                    if (conn.State == ConnectionState.Closed)
                    {
                        record = "Database connection closed";
                    }
                    else if (conn.State == ConnectionState.Broken)
                    {
                        record = "Database connection is destroyed";
                    }
                }
                else
                {
                    record = "Please open the database connection first";
                }
                record = "error";
                return dataSet;
            }
            catch (Exception ex)
            {
                DataSet dataSet = new DataSet();
                record = ex.Message.ToString();
                return dataSet;
            }

        }

    }
}
